package com.stopboot.code.generator.utils;

import com.stopboot.code.generator.enums.DBTypeEnum;
import com.stopboot.code.generator.modle.DbLinkInfo;
import com.stopboot.code.generator.modle.TableBo;
import com.stopboot.code.generator.modle.TableColumnBo;
import com.stopboot.code.generator.modle.TableIndexBo;
import java.util.Objects;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author lyt
 * @version 1.0.0
 * @ClassName DbUtils.java
 * @Description 数据库工具类
 * @createTime 2022年04月24日 16:09:00
 */
@Slf4j
public class DbUtils {

    /**
     * mysql 表信息
     */
    public final static String MYSQL_TABLE_INFO_SQL = "SELECT table_schema, table_name, engine, table_collation, table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA = ( SELECT DATABASE() ) AND table_name = ?";

    public final static String mysqlTableSqlString = "SELECT table_schema, table_name, engine, table_collation, table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA = ( SELECT DATABASE() )";

    public final static String mysqlColumnSqlString = "select ordinal_position, column_name,data_type, is_nullable as nullable, column_comment , column_default , column_key,column_type, character_maximum_length, character_octet_length,numeric_precision,numeric_scale,datetime_precision,character_set_name, collation_name , extra from information_schema.columns where table_schema = ( select database() ) and table_name = ? order by ordinal_position;";

    public final static String mysqlIndexSqlString = "select index_name, column_name, non_unique, index_type, index_comment,cardinality,collation,seq_in_index from information_schema.statistics where table_schema = ( select database() ) and table_name = ? order by index_name,seq_in_index;";

    /**
     * oracle 表信息
     */

    public final static String ORACLE_TABLE_INFO_SQL = "select tc.table_name,tc.comments from user_tab_comments  tc left join user_tables t on  t.table_name = tc.table_name where tc.table_name = ?";

    private static String oracleTableSqlString = "select table_name, comments from user_tab_comments";

    private static String oracleColumnSqlString = "select t.column_name,t.data_type,f.comments,t.data_scale from user_tab_columns t left join user_col_comments f on t.column_name = f.column_name and t.table_name = f.table_name where t.table_name = ?";

    private static String oracleIndexSqlString = "";

    /**
     * sqlserver 表信息
     */
    private static String sqlserverTableSqlString = "select name,type,create_date,modify_date,status,comment from sys.tables";

    private static String sqlserverColumnSqlString = "select name,type,max_length,precision,scale,is_nullable,is_identity,default_value,collation_name from sys.columns where object_id = object_id(?)";

    private static String sqlserverIndexSqlString = "select name,type,is_unique,is_primary_key,is_unique_constraint,is_disabled,is_hypothetical from sys.indexes where object_id = object_id(?)";

    /**
     * sqlite 表信息
     */
    private static String sqliteTableSqlString = "select name,type,create_date,modify_date,status,comment from sqlite_master where type = 'table'";

    private static String sqliteColumnSqlString = "select name,type,max_length,precision,scale,is_nullable,is_identity,default_value,collation_name from sqlite_master where type = 'table' and name = ?";

    private static String sqliteIndexSqlString = "select name,type,is_unique,is_primary_key,is_unique_constraint,is_disabled,is_hypothetical from sqlite_master where type = 'table' and name = ?";


//    /**
//     * oracle 获取表信息
//     *
//     * @param dataSource
//     * @return
//     */
//    private static String oracleTableSqlString = "select table_name, comments from user_tab_comments";
//
//    /**
//     * oracle 获取表字段信息
//     */
//    private static String oracleColumnSqlString = "select t.column_name,t.data_type,f.comments,t.data_scale from user_tab_columns t left join user_col_comments f on t.column_name = f.column_name and t.table_name = f.table_name where t.table_name = ?";
//

    public static DataSource init(DbLinkInfo dbLinkInfo) {
        String url = "";
        if (DBTypeEnum.MYSQL.getType().equals(dbLinkInfo.getDbType())) {
            url = "jdbc:mysql://" + dbLinkInfo.getServerName() + ":" + dbLinkInfo.getPortNumber() + "/" + dbLinkInfo.getDatabaseName();
        }
        if (StringUtils.isEmpty(url)) {
            throw new RuntimeException("数据库类型不支持");
        }
        DataSource dataSource = new DriverManagerDataSource(url, dbLinkInfo.getUser(), dbLinkInfo.getPassword());
        try {
            Connection connection = dataSource.getConnection();
            if (connection != null) {
                return dataSource;
            } else {
                throw new RuntimeException("数据库连接失败");
            }
        } catch (Exception e) {
            log.error("数据库连接失败", e);
            throw new RuntimeException("数据库连接失败");
        }

    }


    public static void close(DataSource dataSource) {
        if (dataSource != null) {
            try {
                dataSource.getConnection().close();
            } catch (SQLException e) {
                e.printStackTrace();
                log.error("关闭数据库连接失败");
            }
        }
    }


    public static TableBo table(DataSource dataSource, String tableName, String dbType) {
        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            String sql = "";
            if (DBTypeEnum.MYSQL.getType().equals(dbType)) {
                sql = MYSQL_TABLE_INFO_SQL;
            } else if (DBTypeEnum.ORACLE.getType().equals(dbType)) {
                sql = ORACLE_TABLE_INFO_SQL;
            }
            if (StringUtils.isBlank(sql)) {
                throw new RuntimeException("数据库类型不支持");
            }
            //获得表信息
            TableBo tableBo = template.queryForObject(sql,
                new Object[] {tableName},
                new BeanPropertyRowMapper<>(TableBo.class));
            return tableBo;
        } catch (Exception e) {
            log.error("{}获取数据库表失败:{}", dbType, e);
            throw new RuntimeException(dbType + "获取数据库表失败");
        } finally {
            close(dataSource);
        }
    }


    /**
     * 查询数据库所有表
     *
     * @return
     */
    public static List<TableBo> tables(DataSource dataSource, String dbType) {
        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            String sql = "";
            if (DBTypeEnum.MYSQL.getType().equalsIgnoreCase(dbType)) {
                sql = mysqlTableSqlString;
            } else if (DBTypeEnum.ORACLE.getType().equalsIgnoreCase(dbType)) {
                sql = oracleTableSqlString;
            }
            if (StringUtils.isBlank(sql)) {
                throw new RuntimeException("数据库类型不支持");
            }
            //获得当前数据库的全部表
            List<TableBo> tableBoList = template.query(sql, new BeanPropertyRowMapper<>(TableBo.class));
            return tableBoList;
        } catch (Exception e) {
            log.error("{}获取数据库表失败:{}", dbType, e);
            throw new RuntimeException(dbType + "获取数据库表失败");
        } finally {
            close(dataSource);
        }

    }

    /**
     * 获取表的字段
     *
     * @param dataSource
     * @param tableName
     * @param dbType
     * @return
     */
    public static List<TableColumnBo> columns(DataSource dataSource, String tableName, String dbType) {
        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            String sql = "";
            if (DBTypeEnum.MYSQL.getType().equalsIgnoreCase(dbType)) {
                sql = mysqlColumnSqlString;
            } else if (DBTypeEnum.ORACLE.getType().equalsIgnoreCase(dbType)) {
                sql = oracleColumnSqlString;
            }
            if (StringUtils.isBlank(sql)) {
                throw new RuntimeException("数据库类型不支持");
            }
            List<TableColumnBo> tableColumnVoList = template.query(
                sql,
                new Object[] {tableName}, new BeanPropertyRowMapper<>(TableColumnBo.class));


            tableColumnVoList.forEach(tableColumnBo -> {
                Integer scale = tableColumnBo.getDataScale();
                if (Objects.nonNull(tableColumnBo.getNumericScale())) {
                    scale = tableColumnBo.getNumericScale().intValue();
                }
                tableColumnBo.setJavaType(TypeUtil.dbTypeToJavaType(tableColumnBo.getDataType(), scale));
                tableColumnBo.setJavaColumnName(StringUtil.underlineToCamel(tableColumnBo.getColumnName()));
                tableColumnBo.setLength(patternLength(tableColumnBo.getColumnType()));
            });
            return tableColumnVoList;
        } catch (Exception e) {
            log.error("{}获取数据库列信息失败:{}", dbType, e);
            throw new RuntimeException(dbType + "获取数据库表字段失败");
        } finally {
            close(dataSource);
        }
    }


    private static Integer patternLength(String str) {
        Pattern pattern = Pattern.compile("\\((.+?)\\)");
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            String result = matcher.group(matcher.groupCount());
            log.info("已匹配：{}", result);
            if (result.contains(",")) {
                String[] split = result.split(",");
                return Integer.valueOf(split[0]);
            }
            return Integer.valueOf(matcher.group(matcher.groupCount()) + "");
        }
        return 0;
    }


    public static List<TableIndexBo> indexs(DataSource dataSource, String tableName, String dbType) {
        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            String sql = "";
            if (DBTypeEnum.MYSQL.getType().equals(dbType)) {
                sql = mysqlIndexSqlString;
            } else if (DBTypeEnum.ORACLE.getType().equals(dbType)) {
                sql = oracleIndexSqlString;
            }
            if (StringUtils.isBlank(sql)) {
                throw new RuntimeException("数据库类型不支持");
            }
            List<TableIndexBo> tableIndexBoList = template.query(
                sql,
                new Object[] {tableName}, new BeanPropertyRowMapper<>(TableIndexBo.class));
            return tableIndexBoList;
        } catch (Exception e) {
            log.error("{}获取数据库索引信息失败:{}", dbType, e);
            throw new RuntimeException(dbType + "获取数据库表字段失败");
        } finally {
            close(dataSource);
        }
    }


    public static final String dbUrl = "jdbc:mysql://localhost:3306/";
    public static final String dbName = "stopboot";
    public static final String dbUsername = "root";
    public static final String dbPassword = "123456";

    public static void main(String[] args) {

        //链接指定数据库
//        DataSource dataSource = new DriverManagerDataSource(dbUrl + dbName, dbUsername, dbPassword);
//
//        List<TableColumnBo> tableColumnBos = columns(dataSource, "kw_db_table", DBTypeEnum.MYSQL.getType());
//
//        System.out.println(tableColumnBos);

//
//        List<TableIndexBo> tableIndexBoList = DbUtils.indexs(dataSource, "kw_db_table", DBTypeEnum.MYSQL);
//
//        log.debug("{}", tableIndexBoList);


    }


}
